package org.skydiveapplibrary.repository;

import java.util.Date;

import org.skydiveapplibrary.data.AltitudeUnit;
import org.skydiveapplibrary.data.Database;
import org.skydiveapplibrary.data.LogbookHistory;
import org.skydiveapplibrary.data.Settings;
import org.skydiveapplibrary.data.Statistics;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class StatisticsRepository
{
	// count query
	private static final String COUNTS_QUERY =
		"select " +
		"max(jump_number) as total_jump_count, " +
		"sum(cutaway) as total_cutaways, " +
		"sum(freefall_time_seconds) as total_freefall_time, " +
		"max(freefall_time_seconds) as max_freefall_time " +
		"from log_entry";
	private static final int TOTAL_JUMPS = 0;
	private static final int TOTAL_CUTAWAYS = 1;
	private static final int TOTAL_FREEFALL_TIME = 2;
	private static final int MAX_FREEFALL_TIME = 3;
	// distances query
	private static final String DISTANCES_QUERY =
		"select " +
		"sum(exit_altitude) as total_exit_altitude, " + 
		"max(exit_altitude) as max_exit_altitude, " +
		"sum(deployment_altitude) as total_deployment_altitude, " +
		"min(deployment_altitude) as min_deployment_altitude " +
		"from log_entry " +
		"where altitude_unit = ?";
	private static final int TOTAL_EXIT_ALT = 0;
	private static final int MAX_EXIT_ALT = 1;
	private static final int TOTAL_DEPL_ALT = 2;
	private static final int MIN_DEPL_ALT = 3;
	// max date
	private static final String MAX_DATE_QUERY = "select max(date) as max_date from log_entry";
	private static final int MAX_DATE = 0;
	
	private static final String JUMPS_LAST_MONTH_QUERY = "select count(*) as total_jumps_count from log_entry where DATE(date) > DATE(current_date, '-1 month')";
	private static final String JUMPS_LAST_YEAR_QUERY = "select count(*) as total_jumps_count from log_entry where DATE(date) > DATE(current_date, '-1 year')";
	private static final int JUMPS_LAST = 0;
	
	private Database database;
	
	// repositories
	private LogbookHistoryRepository historyRepository;
	private SettingsRepository settingsRepository;
	
	public StatisticsRepository(Context context)
	{
		database = new Database(context);
		historyRepository = new LogbookHistoryRepository(context);
		settingsRepository = new SettingsRepository(context);
	}
	
	public Statistics getStatistics()
	{
		Statistics stats = new Statistics();
		
		// get history
		LogbookHistory history = historyRepository.getHistory();
		// get settings
		Settings settings = settingsRepository.getSettings();
		
		SQLiteDatabase db = database.getReadableDatabase();
		try
		{
			// set totals
			setTotals(db, stats, history);
			
			// set distances
			setDistances(db, stats, settings.getAltitudeUnit());
			
			// set max date
			setMaxDate(db, stats);
			
			// jumps in last year
			setJumpsLastYear(db, stats);
			// jumps in last month
			setJumpsLastMonth(db, stats);
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			// close db
			db.close();
		}
		
		return stats;
	}
	
	private void setTotals(SQLiteDatabase db, Statistics stats, LogbookHistory history)
	{
		Cursor cursor = null;
		try
		{
			cursor = db.rawQuery(COUNTS_QUERY, null);
			if (cursor.moveToFirst())
			{
				stats.setTotalJumpCount(cursor.getInt(TOTAL_JUMPS));
				stats.setTotalCutawayCount(
						cursor.getInt(TOTAL_CUTAWAYS) + history.getCutaways());
				stats.setTotalFreefallTime(
						cursor.getInt(TOTAL_FREEFALL_TIME) + history.getFreefallTime());
				stats.setMaxFreefallTime(cursor.getInt(MAX_FREEFALL_TIME));
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
	}
	
	private void setDistances(SQLiteDatabase db, Statistics stats, AltitudeUnit altitudeUnit)
	{
		Cursor cursor = null;
		try
		{
			// set altitude unit
			stats.setAltitudeUnit(altitudeUnit);
			
			// feet query
			int totalExitAltFeet = 0;
			int maxExitAltFeet = 0;
			int totalDeplAltFeet = 0;
			int minDeplAltFeet = 0;
			cursor = db.rawQuery(DISTANCES_QUERY, new String[] { AltitudeUnit.Feet.toString() });
			if (cursor.moveToFirst())
			{
				totalExitAltFeet = cursor.getInt(TOTAL_EXIT_ALT);
				maxExitAltFeet = cursor.getInt(MAX_EXIT_ALT);
				totalDeplAltFeet = cursor.getInt(TOTAL_DEPL_ALT);
				minDeplAltFeet = cursor.getInt(MIN_DEPL_ALT);
			}
			// meters query
			int totalExitAltMeters = 0;
			int maxExitAltMeters = 0;
			int totalDeplAltMeters = 0;
			int minDeplAltMeters = 0;
			cursor = db.rawQuery(DISTANCES_QUERY, new String[] { AltitudeUnit.Meters.toString() });
			if (cursor.moveToFirst())
			{
				totalExitAltMeters = cursor.getInt(TOTAL_EXIT_ALT);
				maxExitAltMeters = cursor.getInt(MAX_EXIT_ALT);
				totalDeplAltMeters = cursor.getInt(TOTAL_DEPL_ALT);
				minDeplAltMeters = cursor.getInt(MIN_DEPL_ALT);
			}
			
			// get total freefall distance
			float totalExitAlt = 
				AltitudeUnit.convert(totalExitAltFeet, AltitudeUnit.Feet, altitudeUnit) +
				AltitudeUnit.convert(totalExitAltMeters, AltitudeUnit.Meters, altitudeUnit);
			float totalDeplAlt =
				AltitudeUnit.convert(totalDeplAltFeet, AltitudeUnit.Feet, altitudeUnit) +
				AltitudeUnit.convert(totalDeplAltMeters, AltitudeUnit.Meters, altitudeUnit);
			stats.setTotalFreefallDistance(Math.round(totalExitAlt - totalDeplAlt));
			// set min/max altitudes
			float maxExitAlt = Math.max(
						AltitudeUnit.convert(maxExitAltFeet, AltitudeUnit.Feet, altitudeUnit),
						AltitudeUnit.convert(maxExitAltMeters, AltitudeUnit.Meters, altitudeUnit));
			stats.setMaxExitAltitude(Math.round(maxExitAlt));
			float minDeplAlt = Math.max(
					AltitudeUnit.convert(minDeplAltFeet, AltitudeUnit.Feet, altitudeUnit),
					AltitudeUnit.convert(minDeplAltMeters, AltitudeUnit.Meters, altitudeUnit));
			stats.setMinDeploymentAltitude(Math.round(minDeplAlt));
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
	}
	
	private void setMaxDate(SQLiteDatabase db, Statistics stats)
	{
		Cursor cursor = null;
		Date lastJump = new Date();
		try
		{
			cursor = db.rawQuery(MAX_DATE_QUERY, null);
			if (cursor.moveToFirst() && !cursor.isNull(MAX_DATE))
			{
				lastJump = Database.stringToDate(cursor.getString(MAX_DATE));
			}
			stats.setLastJumpDate(lastJump);
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
	}
	
	private void setJumpsLastMonth(SQLiteDatabase db, Statistics stats)
	{
		Cursor cursor = null;
		try
		{
			cursor = db.rawQuery(JUMPS_LAST_MONTH_QUERY, null);
			if (cursor.moveToFirst())
			{
				stats.setJumpsInLastMonth(cursor.getInt(JUMPS_LAST));
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
	}
	
	private void setJumpsLastYear(SQLiteDatabase db, Statistics stats)
	{
		Cursor cursor = null;
		try
		{
			cursor = db.rawQuery(JUMPS_LAST_YEAR_QUERY, null);
			if (cursor.moveToFirst())
			{
				stats.setJumpsInLastYear(cursor.getInt(JUMPS_LAST));
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
	}
}